import { Transaction, Category } from '../models/index.js';
import { Op } from 'sequelize';
import sequelize from '../config/database.js';

// 获取统计概览
export const getOverview = async (req, res) => {
  try {
    const { startDate, endDate } = req.query;
    
    // 构建时间查询条件
    const dateQuery = {};
    if (startDate || endDate) {
      dateQuery.date = {};
      if (startDate) {
        dateQuery.date[Op.gte] = new Date(startDate);
      }
      if (endDate) {
        dateQuery.date[Op.lte] = new Date(endDate);
      }
    }

    // 获取总收入
    const totalIncomeResult = await Transaction.findAll({
      where: { type: 'income', ...dateQuery },
      attributes: [[sequelize.fn('SUM', sequelize.col('amount')), 'total']],
      raw: true
    });
    const totalIncome = parseFloat(totalIncomeResult[0]?.total || 0);

    // 获取总支出
    const totalExpenseResult = await Transaction.findAll({
      where: { type: 'expense', ...dateQuery },
      attributes: [[sequelize.fn('SUM', sequelize.col('amount')), 'total']],
      raw: true
    });
    const totalExpense = parseFloat(totalExpenseResult[0]?.total || 0);

    // 获取交易笔数
    const transactionCount = await Transaction.count({ where: dateQuery });

    // 获取收入笔数
    const incomeCount = await Transaction.count({
      where: { type: 'income', ...dateQuery }
    });

    // 获取支出笔数
    const expenseCount = await Transaction.count({
      where: { type: 'expense', ...dateQuery }
    });

    res.json({
      status: 'success',
      data: {
        totalIncome,
        totalExpense,
        balance: totalIncome - totalExpense,
        transactionCount,
        incomeCount,
        expenseCount
      }
    });
  } catch (error) {
    res.status(500).json({
      status: 'error',
      message: '获取统计概览失败',
      error: error.message
    });
  }
};

// 获取按分类的统计
export const getCategoryStats = async (req, res) => {
  try {
    const { startDate, endDate, type } = req.query;
    
    // 构建查询条件
    const whereQuery = {};
    if (type) {
      whereQuery.type = type;
    }
    if (startDate || endDate) {
      whereQuery.date = {};
      if (startDate) {
        whereQuery.date[Op.gte] = new Date(startDate);
      }
      if (endDate) {
        whereQuery.date[Op.lte] = new Date(endDate);
      }
    }

    const categoryStats = await Transaction.findAll({
      where: whereQuery,
      attributes: [
        'categoryId',
        'type',
        [sequelize.fn('SUM', sequelize.col('Transaction.amount')), 'totalAmount'],
        [sequelize.fn('COUNT', '*'), 'count']
      ],
      include: [{
        model: Category,
        as: 'category',
        attributes: ['id', 'name', 'icon', 'color']
      }],
      group: ['Transaction.categoryId', 'Transaction.type', 'category.id', 'category.name', 'category.icon', 'category.color'],
      order: [[sequelize.fn('SUM', sequelize.col('Transaction.amount')), 'DESC']],
      raw: false
    });

    const formattedStats = categoryStats.map(stat => ({
      categoryId: stat.categoryId,
      totalAmount: parseFloat(stat.get('totalAmount')),
      count: parseInt(stat.get('count')),
      type: stat.type,
      categoryName: stat.category?.name,
      categoryIcon: stat.category?.icon,
      categoryColor: stat.category?.color
    }));

    res.json({
      status: 'success',
      data: { categoryStats: formattedStats }
    });
  } catch (error) {
    res.status(500).json({
      status: 'error',
      message: '获取分类统计失败',
      error: error.message
    });
  }
};

// 获取按月份的统计
export const getMonthlyStats = async (req, res) => {
  try {
    const { year = new Date().getFullYear() } = req.query;
    
    const monthlyStats = await Transaction.findAll({
      where: {
        date: {
          [Op.gte]: new Date(`${year}-01-01`),
          [Op.lte]: new Date(`${year}-12-31`)
        }
      },
      attributes: [
        [sequelize.fn('strftime', '%m', sequelize.col('date')), 'month'],
        'type',
        [sequelize.fn('SUM', sequelize.col('amount')), 'totalAmount'],
        [sequelize.fn('COUNT', '*'), 'count']
      ],
      group: [sequelize.fn('strftime', '%m', sequelize.col('date')), 'type'],
      order: [[sequelize.fn('strftime', '%m', sequelize.col('date')), 'ASC']],
      raw: true
    });

    // 按月份组织数据
    const monthlyData = {};
    monthlyStats.forEach(stat => {
      const month = stat.month;
      if (!monthlyData[month]) {
        monthlyData[month] = {
          income: 0,
          expense: 0,
          incomeCount: 0,
          expenseCount: 0
        };
      }
      
      if (stat.type === 'income') {
        monthlyData[month].income = parseFloat(stat.totalAmount);
        monthlyData[month].incomeCount = parseInt(stat.count);
      } else if (stat.type === 'expense') {
        monthlyData[month].expense = parseFloat(stat.totalAmount);
        monthlyData[month].expenseCount = parseInt(stat.count);
      }
    });

    // 填充缺失的月份数据
    const fullYearStats = [];
    for (let month = 1; month <= 12; month++) {
      const monthData = monthlyData[month] || {
        income: 0,
        expense: 0,
        incomeCount: 0,
        expenseCount: 0
      };
      
      fullYearStats.push({
        month,
        income: monthData.income,
        expense: monthData.expense,
        incomeCount: monthData.incomeCount,
        expenseCount: monthData.expenseCount,
        balance: monthData.income - monthData.expense
      });
    }

    res.json({
      status: 'success',
      data: {
        year: parseInt(year),
        monthlyStats: fullYearStats
      }
    });
  } catch (error) {
    res.status(500).json({
      status: 'error',
      message: '获取月度统计失败',
      error: error.message
    });
  }
};

// 获取按账户类型的统计
export const getAccountStats = async (req, res) => {
  try {
    const { startDate, endDate } = req.query;
    
    // 构建查询条件
    const whereQuery = {};
    if (startDate || endDate) {
      whereQuery.date = {};
      if (startDate) {
        whereQuery.date[Op.gte] = new Date(startDate);
      }
      if (endDate) {
        whereQuery.date[Op.lte] = new Date(endDate);
      }
    }

    const accountStats = await Transaction.findAll({
      where: whereQuery,
      attributes: [
        'account',
        'type',
        [sequelize.fn('SUM', sequelize.col('amount')), 'totalAmount'],
        [sequelize.fn('COUNT', '*'), 'count']
      ],
      group: ['account', 'type'],
      order: [['account', 'ASC']],
      raw: true
    });

    // 按账户类型组织数据
    const accountData = {};
    accountStats.forEach(stat => {
      const account = stat.account;
      if (!accountData[account]) {
        accountData[account] = {
          income: 0,
          expense: 0,
          incomeCount: 0,
          expenseCount: 0
        };
      }
      
      if (stat.type === 'income') {
        accountData[account].income = parseFloat(stat.totalAmount);
        accountData[account].incomeCount = parseInt(stat.count);
      } else if (stat.type === 'expense') {
        accountData[account].expense = parseFloat(stat.totalAmount);
        accountData[account].expenseCount = parseInt(stat.count);
      }
    });

    // 添加账户类型的中文名称
    const accountNames = {
      cash: '现金',
      bank_card: '银行卡',
      alipay: '支付宝',
      wechat: '微信',
      credit_card: '信用卡',
      other: '其他'
    };

    const formattedStats = Object.keys(accountData).map(account => ({
      account,
      accountName: accountNames[account] || account,
      income: accountData[account].income,
      expense: accountData[account].expense,
      incomeCount: accountData[account].incomeCount,
      expenseCount: accountData[account].expenseCount,
      balance: accountData[account].income - accountData[account].expense
    }));

    res.json({
      status: 'success',
      data: { accountStats: formattedStats }
    });
  } catch (error) {
    res.status(500).json({
      status: 'error',
      message: '获取账户统计失败',
      error: error.message
    });
  }
};